Release 10.1A: OpenEdge Data Management:
Database Administration

SQLSCHEMA utility

A command-line utility that writes SQL database schema components to an output file selectively.

Syntax

sqlschema -u user_name [ -a password ]           [ -t [ owner_name.]table_name1 [,owner_name.]table_name2, ... ]           [ -p [ owner_name.]procedure_name, ... ]           [ -T [ owner_name.]trigger_name, ... ]           [ -g [ owner_name.]table_name, ... ]           [ -s [ owner_name.]tablename, ... ]           [ -o output_file_name ]           db_name

Parameters

-u user_name

Specifies the user id that SQLSCHEMA employs to connect to the database. If you omit the user_name and password, SQLSCHEMA prompts you for these values. If you omit the user_name and supply a password, SQLSCHEMA uses the value defined by the USER environment variable.

-a password

Specifies the password used by the database for authentication.

-t owner_name.table_name

A list of one or more tables you want to capture definitions for. Pattern matching is supported, using a percent sign (%) for multiple characters and an underscore (_) for a single character. The pattern matching follows the standard for the LIKE predicate in SQL. You can write the definition for a single table, a set of tables, or all tables. If you omit the optional owner_name table qualifier, SQLSCHEMA uses the name specified by the -u parameter.

-p owner_name.procedure_name

A list of one or more procedures you want to capture definitions for. The SQLSCHEMA utility supports pattern matching for multiple and single characters. See the owner_name.table_name parameter for an explanation of pattern matching. You can capture the definitions for a single procedure, a set of procedures, or all procedures. If you omit the optional owner_name table qualifier, SQLSCHEMA uses the name specified by the -u parameter.

-T owner_name.trigger_name

A list of one or more triggers you want to capture definitions for. The SQLSCHEMA utility supports pattern matching for multiple and single characters. See the owner_name.table_name parameter for an explanation of pattern matching. You can capture the definition for a single trigger, a set of triggers, or all triggers. If you omit the optional owner_name table qualifier, SQLSCHEMA uses the name specified by the -u parameter.

-g owner_name.table_name

A list of one or more tables whose related privileges are captured as grant statements. You can write grant statements for both column and table privileges. The utility supports pattern matching for this parameter.

-s owner_name.table_name

Specifies a list of one or more tables whose related synonyms are captured as create synonym statements. The utility supports pattern matching for this parameter.

-o output_file_name.dfsql

Specifies the output file where SQLSCHEMA writes the definitions. When specified, the file extension name must be .dfsql. If output_file_name is omitted, SQLSCHEMA writes the definitions to the screen.

db_name

Identifies the database from which SQLSCHEMA captures component definitions. You can process a single database each time you invoke SQLSCHEMA. There is no option flag preceding the db_name. This parameter is required and must be the last parameter specified. The database name is specified in a connection string, such as progress:T:localhost:demosv:jo.

SQLSCHEMA writes SQL database schema components to an output file selectively. You can capture table definitions including table constraints, views, stored procedures including related privileges, and triggers. At the command line you specify which components to dump. To load database schema information into a database, use the SQL Explorer tool. See OpenEdge Data Management: SQL Reference for information about SQL Explorer.

The SQLSCHEMA utility cannot write definitions for Progress 4GL tables. Table definitions include the database area name for the table, derived from a scan of the area and objects. When SQLSCHEMA writes a table definition, it does not automatically write associated triggers, synonyms, or privileges. These must be explicitly specified on the command line. Capturing database schema requires privileges to access the requested components.

Examples

This example directs the SQLSCHEMA utility to write table definitions and trigger information. The output goes to the screen since no output_file_name is specified. Since the user name and password are not specified, SQLSCHEMA will prompt the user for these values:

sqlschema -t tucker.customers,tucker.products -T tucker.customers,tucker.products progress:T:thunder:4077:salesdb

This example directs the SQLSCHEMA utility to write table definitions to an output file named salesdbschema.dfsql:

sqlschema -u tucker -a sulky -t %.cust%,%.invent%,%.sales% -o salesdbschema.dfsql progress:T:thunder:4077:salesdb

Notes

Before you can execute SQLSCHEMA against a database server, the server must be configured to accept SQL connections and must be running.
Each output file created by the SQLSCHEMA utility records character set information about the contents of the file. When you use SQLSCHEMA to dump schema information from a database, the schema is written in Unicode UTF-8.